SET SERVEROUTPUT ON SIZE 1000000;
SET VERIFY OFF

DECLARE
	l_total_extracted	NUMBER;
	l_total_imported	NUMBER;
	l_total_extracted_2	NUMBER;
	l_total_imported_2	NUMBER;
	l_total_extracted_3	NUMBER;
	l_total_imported_3	NUMBER;				
	l_func_id			VARCHAR2(100):='CAPPOREC_POSITION_HIERARCHY';
	l_code VARCHAR2(100):=UPPER('&1');

BEGIN
	SELECT COUNT(1)
	INTO l_total_extracted        
	from   PER_POSITION_STRUCTURES PPS, 
		   HR_ORGANIZATION_UNITS HOU
	where  pps.business_group_id = hou.business_group_id 
   	and    upper(hou.name) = 'BG_'||upper(substr(l_code, -4));
   	/*and upper(hou.name) =(	SELECT 'BG_'||upper(to_char(set_of_books_id))
          					FROM   gl_sets_of_books
     						WHERE  upper(short_name) = upper(l_code));*/
	--AND    upper(hou.name) = upper(l_code);        						
	
	SELECT COUNT(1) 
	INTO l_total_imported
	FROM CRST_PER_POSITION_STRUCTURES;

	SELECT COUNT(1)
	INTO l_total_extracted_2
	from   PER_POS_STRUCTURE_VERSIONS ppsv 
	where  ppsv.BUSINESS_GROUP_ID in (select pps.BUSINESS_GROUP_ID
						 from   PER_POSITION_STRUCTURES PPS, 
		   					  HR_ORGANIZATION_UNITS HOU
						 where  pps.business_group_id = hou.business_group_id 
					      and    upper(hou.name) = 'BG_'||upper(substr(l_code, -4))
					     	/* and upper(hou.name) =(	SELECT 'BG_'||upper(to_char(set_of_books_id))
          					FROM   gl_sets_of_books
     						WHERE  upper(short_name) = upper(l_code))*/
						 )
	and ppsv.POSITION_STRUCTURE_ID in (select pps.POSITION_STRUCTURE_ID
						 from   PER_POSITION_STRUCTURES PPS, 
		   					  HR_ORGANIZATION_UNITS HOU
						 where  pps.business_group_id = hou.business_group_id 
					     	 and    upper(hou.name) = 'BG_'||upper(substr(l_code, -4))
					     	/* and upper(hou.name) =(	SELECT 'BG_'||upper(to_char(set_of_books_id))
          					FROM   gl_sets_of_books
     						WHERE  upper(short_name) = upper(l_code))*/
						 );

	SELECT COUNT(1) 
	INTO l_total_imported_2
	FROM CRST_PER_POS_STRUCTURE_VERSION;

	select COUNT(1)
	INTO l_total_extracted_3
	from   PER_POS_STRUCTURE_VERSIONS ppsv ,
		   PER_POSITION_STRUCTURES PPS, 
		   HR_ORGANIZATION_UNITS HOU,
		   PER_POS_STRUCTURE_ELEMENTS ppse 
	where  ppsv.BUSINESS_GROUP_ID=pps.BUSINESS_GROUP_ID 
	and	   ppsv.POSITION_STRUCTURE_ID=pps.POSITION_STRUCTURE_ID
	AND    pps.business_group_id = hou.business_group_id
	and    ppse.BUSINESS_GROUP_ID=ppsv.BUSINESS_GROUP_ID 
	and    ppse.POS_STRUCTURE_VERSION_ID=ppsv.POS_STRUCTURE_VERSION_ID
    and    upper(hou.name) = 'BG_'||upper(substr(l_code, -4));

						    
	SELECT COUNT(1)
	INTO l_total_imported_3
	FROM CRST_PER_POS_STRUCTURE_ELEMENT;

	dbms_output.put_line('Function: '||l_func_id);		
	dbms_output.put_line('Total Extracted: '||TO_CHAR(l_total_extracted)) ;
	dbms_output.put_line('Total Imported:  '||TO_CHAR(l_total_imported));
	dbms_output.put_line('Total Extracted_2: '||TO_CHAR(l_total_extracted_2)) ;
	dbms_output.put_line('Total Imported_2:  '||TO_CHAR(l_total_imported_2));
	dbms_output.put_line('Total Extracted_3: '||TO_CHAR(l_total_extracted_3)) ;
	dbms_output.put_line('Total Imported_3:  '||TO_CHAR(l_total_imported_3));
	IF l_total_extracted <> l_total_imported THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;		
	IF l_total_extracted_2 <> l_total_imported_2 THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;
	IF l_total_extracted_3 <> l_total_imported_3 THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;
						
EXCEPTION
	WHEN OTHERS THEN	
		dbms_output.put_line('Function Error: '||l_func_id);			
END;
/
